package board1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class BoardDAO {
	String db_driver;
	String db_url;
	String db_id;
	String db_passwd;
	String tb_name;

	public BoardDAO() {
		db_driver = "com.mysql.jdbc.Driver";
//		db_url = "jdbc:mysql://localhost/android?useUnicode=true&characterEncoding=utf-8";
//		db_id = "root";
//		db_passwd = "12345";
		
		db_url = "jdbc:mysql://localhost/auralizer?useUnicode=true&characterEncoding=utf-8";
		db_id = "auralizer";
		db_passwd = "!234asdf";
		
		tb_name = "sa_board";
	}
	
	public boolean insertBoard(BoardTO to) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		boolean flag = false; 
		
		try {		
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
			String sql = "insert into " + tb_name + " values (0, ?, ?, ?, ?, ?, 0, 0, now(),?,now(),?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, to.getChon_id());
			pstmt.setString(2, to.getBoard_id());
			pstmt.setString(3, to.getPhone_number());
			pstmt.setString(4, to.getTitle());
			pstmt.setString(5, to.getContent());
//			pstmt.setString(6, to.getView_lv());
//			pstmt.setString(6, to.getWr_dt());
			pstmt.setString(6, to.getFrom_dt());
//			pstmt.setString(8, to.getEnd_dt());
			pstmt.setString(7, to.getPic_name());
			
			pstmt.executeUpdate();
			
			flag = true;
		} catch(ClassNotFoundException e) {
		} catch(SQLException e) {
		} finally {
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return flag;
	}

	public ArrayList<BoardTO> listBoard(String board_id, String chon_id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		ArrayList<BoardTO> data = new ArrayList<BoardTO>();
		
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
			String sql = "select idx, chon_id, board_id, phone_number, title, cnt, content, wr_dt, pic_name from " + tb_name +" where board_id=? and chon_id=? order by wr_dt desc";
//			pstmt = conn.prepareStatement(
//						sql,
//						ResultSet.TYPE_SCROLL_INSENSITIVE,
//						ResultSet.CONCUR_READ_ONLY
//					);
//			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, board_id);
			pstmt.setString(2, chon_id);
			
			rs = pstmt.executeQuery();

			while(rs.next()) {
				BoardTO to = new BoardTO();
				to.setIdx(rs.getString("idx"));
				to.setChon_id(rs.getString("chon_id"));
				to.setBoard_id(rs.getString("board_id"));
				to.setPhone_number(rs.getString("phone_number"));
				to.setTitle(rs.getString("title"));
				to.setContent(rs.getString("content"));
				to.setWr_dt(rs.getString("wr_dt"));
				to.setHit(rs.getString("cnt"));
				to.setPic_name(rs.getString("pic_name"));					
				data.add(to);
			}
		} catch(ClassNotFoundException e) {
		} catch(SQLException e) {
		} finally {
			if(rs != null) try { rs.close(); } catch(SQLException e) {}
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return data;
	}
	
	public ArrayList<BoardTO> listBoard() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		ArrayList<BoardTO> data = new ArrayList<BoardTO>();
		
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
			String sql = "select idx, chon_id, board_id, phone_number, title, cnt, content, wr_dt, pic_name from " + tb_name;
//			pstmt = conn.prepareStatement(
//						sql,
//						ResultSet.TYPE_SCROLL_INSENSITIVE,
//						ResultSet.CONCUR_READ_ONLY
//					);
//			
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();

			while(rs.next()) {
				BoardTO to = new BoardTO();
				to.setIdx(rs.getString("idx"));
				to.setChon_id(rs.getString("chon_id"));
				to.setBoard_id(rs.getString("board_id"));
				to.setPhone_number(rs.getString("phone_number"));
				to.setTitle(rs.getString("title"));
				to.setContent(rs.getString("content"));
				to.setWr_dt(rs.getString("wr_dt"));
				to.setHit(rs.getString("cnt"));
				to.setPic_name(rs.getString("pic_name"));					
				data.add(to);
			}
		} catch(ClassNotFoundException e) {
		} catch(SQLException e) {
		} finally {
			if(rs != null) try { rs.close(); } catch(SQLException e) {}
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return data;
	}	
	
//	public BoardListTO listBoard(BoardListTO tos) {
//		Connection conn = null;
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//		
//		int cpage = tos.getCpage();
//		int recordPerPage = tos.getRecordPerPage();
//		int blockPerPage = tos.getBlockPerPage();
//		
//		try {
//			Class.forName(db_driver);
//			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
//			
//			String sql = "select no, subject, writer, wdate, hit from " + tb_name + " order by no desc";
//			pstmt = conn.prepareStatement(
//						sql,
//						ResultSet.TYPE_SCROLL_INSENSITIVE,
//						ResultSet.CONCUR_READ_ONLY
//					);
//			
//			rs = pstmt.executeQuery();
//			
//			rs.last();
//			int totalRecord = rs.getRow();
//			rs.beforeFirst();
//			
//			tos.setTotalPage((totalRecord > 0 && totalRecord % recordPerPage == 0) ? totalRecord/recordPerPage : totalRecord/recordPerPage + 1);
//		
//			int skip = (cpage - 1) * recordPerPage;
//			if(skip != 0) rs.absolute(skip);
//			
//			ArrayList<BoardTO> data = new ArrayList<BoardTO>();
//			for(int i=0 ; i < recordPerPage && rs.next() ; i++) {
//				BoardTO to = new BoardTO();
//				to.setNo(rs.getString("no"));
//				to.setSubject(rs.getString("subject"));
//				to.setWriter(rs.getString("writer"));
//				to.setWdate(rs.getString("wdate"));
//				to.setHit(rs.getString("hit"));
//				data.add(to);
//			}
//			tos.setBoardList(data);
//			
//			tos.setStartBlock(((cpage-1) / blockPerPage) * blockPerPage + 1);
//			tos.setEndBlock(((cpage-1) / blockPerPage) * blockPerPage + blockPerPage);
//			if(tos.getEndBlock() >= tos.getTotalPage()) {
//				tos.setEndBlock(tos.getTotalPage());
//			}
//		} catch(ClassNotFoundException e) {
//		} catch(SQLException e) {
//		} finally {
//			if(rs != null) try { rs.close(); } catch(SQLException e) {}
//			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
//			if(conn != null) try { conn.close(); } catch(SQLException e) {}
//		}
//		return tos;
//	}
//	
	
	public BoardTO viewBoard(String idx) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		BoardTO to = new BoardTO();
		
		to.setIdx(idx);
		
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
			String sql = "update " + tb_name + " set cnt=cnt+1 where idx=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, to.getIdx());
			
			pstmt.executeUpdate();
			
			sql = "select chon_id, board_id, phone_number, title, content, wr_dt, pic_name from " + tb_name + " where idx=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, to.getIdx());
			
			rs = pstmt.executeQuery();
			if(rs.next()) {
				to.setChon_id(rs.getString("chon_id"));
				to.setBoard_id(rs.getString("board_id"));
				to.setPhone_number(rs.getString("phone_number"));// == null ? "" : rs.getString("mail"));
				to.setTitle(rs.getString("title"));
				to.setContent(rs.getString("content"));
				to.setWr_dt(rs.getString("wr_dt"));
				to.setPic_name(rs.getString("pic_name"));// == null ? "" : rs.getString("content").replaceAll("\n", "<br>"));
			}
		} catch(ClassNotFoundException e) {
		} catch(SQLException e) {
		} finally {
			if(rs != null) try { rs.close(); } catch(SQLException e) {}
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return to;
	}
//
//	public BoardTO modifyFormBoard(BoardTO to) {
//		Connection conn = null;
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//		try {
//			Class.forName(db_driver);
//			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
//			
//			String sql = "select subject, writer, mail, content from " + tb_name + " where no=?";
//			pstmt = conn.prepareStatement(sql);
//			pstmt.setString(1, to.getNo());
//			
//			rs = pstmt.executeQuery();
//			if(rs.next()) {
//				to.setSubject(rs.getString("subject"));
//				to.setWriter(rs.getString("writer"));
//				to.setMail(rs.getString("mail") == null ? "" : rs.getString("mail"));
//				to.setContent(rs.getString("content") == null ? "" : rs.getString("content"));
//			}
//		} catch(ClassNotFoundException e) {	
//		} catch(SQLException e) {
//		} finally {
//			if(rs != null) try { rs.close(); } catch(SQLException e) {}
//			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
//			if(conn != null) try { conn.close(); } catch(SQLException e) {}
//		}
//		return to;
//	}
//	
//	public BoardTO deleteFormBoard(BoardTO to) {
//		Connection conn = null;
//		PreparedStatement pstmt = null;
//		ResultSet rs = null;
//
//		try {
//			Class.forName(db_driver);
//			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
//			
//			String sql = "select subject, writer, mail from " + tb_name + " where no=?";
//			pstmt = conn.prepareStatement(sql);
//			pstmt.setString(1, to.getNo());
//			
//			rs = pstmt.executeQuery();
//			if(rs.next()) {
//				to.setSubject(rs.getString("subject"));
//				to.setWriter(rs.getString("writer"));
//				to.setMail(rs.getString("mail") == null ? "" : rs.getString("mail"));
//			}
//		} catch(ClassNotFoundException e) {
//		} catch(SQLException e) {
//		} finally {
//			if(rs != null) try { rs.close(); } catch(SQLException e) {}
//			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
//			if(conn != null) try { conn.close(); } catch(SQLException e) {}
//		}
//		return to;
//	}
//
//	public int modifyBoard(BoardTO to) {
//		Connection conn = null;
//		PreparedStatement pstmt = null;
//		
//		int flag = 2;
//		
//		try {
//			Class.forName(db_driver);
//			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
//			
//			String sql = "update " + tb_name + " set subject=?, mail=?, content=? where no=? and passwd=?";
//			pstmt = conn.prepareStatement(sql);
//			pstmt.setString(1, to.getSubject());
//			pstmt.setString(2, to.getMail());
//			pstmt.setString(3, to.getContent());
//			pstmt.setString(4, to.getNo());
//			pstmt.setString(5, to.getPasswd());
//			
//			if(pstmt.executeUpdate() == 1) {
//				flag = 0;
//			} else {
//				flag = 1;
//			}
//		} catch(ClassNotFoundException e) {
//		} catch(SQLException e) {
//		} finally {
//			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
//			if(conn != null) try { conn.close(); } catch(SQLException e) {}
//		}
//		return flag;
//	}
//
	public boolean deleteBoard(BoardTO to) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		boolean flag = false;
		
		System.out.println(to.getIdx()+"===================================="+ to.getPhone_number());
		
		try {
			Class.forName(db_driver);
			conn = DriverManager.getConnection(db_url, db_id, db_passwd);
			
			String sql = "delete from " + tb_name + " where idx=? and phone_number=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, to.getIdx());
			pstmt.setString(2, to.getPhone_number());
			
//			if(pstmt.executeUpdate() == 1) {
//				flag = 0;
//			} else {
//				flag = 1;
//			}
			
		pstmt.executeUpdate();
		
		flag = true;
		
		} catch(Exception e) {
			System.out.println(e.toString());
			e.printStackTrace();
		} finally {
			if(pstmt != null) try { pstmt.close(); } catch(SQLException e) {}
			if(conn != null) try { conn.close(); } catch(SQLException e) {}
		}
		return flag;
	}

}